import os
import pandas as pd
import pymysql
from exts import db

database_name = 'jizu202501'


def data_process(dir, hid):
    # 连接到MySQL数据库
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='A310a310',
        database=database_name,
        cursorclass=pymysql.cursors.DictCursor  # 使用字典游标以便获取列名
    )

    # 创建一个游标
    cursor = connection.cursor()

    # 查询数据库中的数据
    query = "SELECT class_id1, class_id2 FROM student"
    cursor.execute(query)

    # 获取查询结果
    data = cursor.fetchall()

    # 创建一个存储数据的DataFrame
    data_df = pd.DataFrame(data)

    # 关闭数据库连接
    cursor.close()
    connection.close()

    # 创建一个目录用于存储xlsx文件
    if not os.path.exists(dir):
        os.makedirs(dir)
    file_name_list = []
    # 根据class_id2的不同值创建不同的xlsx文件
    unique_class_id2_values = data_df['class_id2'].unique()
    for class_id2_value in unique_class_id2_values:
        # 创建一个新的DataFrame，包含class_id2匹配的数据
        class_id2_data = data_df[data_df['class_id2'] == class_id2_value]

        # 在新的DataFrame中统计每个class_id1的出现次数并添加到新列中
        class_id1_counts = class_id2_data['class_id1'].value_counts().reset_index()
        class_id1_counts.columns = ['class_id1', 'class_id1出现次数']

        # 创建一个以class_id2的值为文件名的xlsx文件
        file_name = os.path.join(dir, f"{class_id2_value}.xlsx")
        # 将file_name记录到file_name_list中
        file_name_list.append(f"{class_id2_value}.xlsx")

        # 将class_id1的出现次数信息合并到原始DataFrame中
        class_id2_data = class_id2_data.drop_duplicates(subset=['class_id1'])
        class_id2_data = class_id2_data.merge(class_id1_counts, on='class_id1', how='left')

        # 调整列的顺序
        class_id2_data = class_id2_data[['class_id1', 'class_id2', 'class_id1出现次数']]

        # 将数据写入xlsx文件
        class_id2_data.to_excel(file_name, index=False)
        # 在dir目录下创建一个class.xlsx文件，存储不同class_id2的人数
        # 统计不同class_id2的人数，存入class.xlsx文件
        class_file_name = os.path.join(dir, 'class.xlsx')

        # 计算class_id2的人数
        class_id2_counts = data_df['class_id2'].value_counts().reset_index()
        class_id2_counts.columns = ['class_id2', '班级人数']

        # 使用pd.ExcelWriter创建一个新的class.xlsx文件，存在则覆盖
        with pd.ExcelWriter(class_file_name, engine='openpyxl', mode='w') as writer:
            class_id2_counts.to_excel(writer, index=False)

        # # 统计不同class_id2的人数，存入class.xlsx文件
        # # 新建一个class.xlsx文件，存在则覆盖

        # class_file_name = os.path.join(dir, 'class.xlsx')

        # # 计算class_id2的人数
        # class_id2_counts = data_df['class_id2'].value_counts().reset_index()
        # class_id2_counts.columns = ['class_id2', '班级人数']

        # # 使用pd.ExcelWriter覆盖写入class_id2_counts到class.xlsx文件，没有则创建
        # with pd.ExcelWriter(class_file_name, engine='openpyxl', mode='w', if_sheet_exists='replace') as writer:
        #     class_id2_counts.to_excel(writer, index=False)
        # # with pd.ExcelWriter(class_file_name, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
        # #     class_id2_counts.to_excel(writer, index=False) 
    file_name_list.append('class.xlsx')
    print("数据处理完成！")
    counter(dir, file_name_list)
    data_save(dir, hid)


def counter(dir, f_list):
    # 分别处理1.xlsx和3.xlsx文件
    for file_name in f_list:
        # 读取文件
        with pd.ExcelFile(os.path.join(dir, file_name)) as excel:
            # 在文件中添加两列，名称分别为抄袭人数和版本数
            df = pd.read_excel(excel)
            df['抄袭人数'] = 0
            df['版本数'] = 0
            df['temp'] = 0
            # 保存修改后的DataFrame回原始Excel文件，使用 'replace' 来覆盖已存在的工作表
            with pd.ExcelWriter(os.path.join(dir, file_name), engine='openpyxl', mode='a',
                                if_sheet_exists='replace') as writer:
                df.to_excel(writer, index=False)

    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='A310a310',
        database=database_name,
        cursorclass=pymysql.cursors.DictCursor  # 使用字典游标以便获取列名
    )

    # 创建一个游标
    cursor = connection.cursor()

    # 从数据库中读取student表的数据
    query = "SELECT ID, class_id2, class_id1 FROM student"
    cursor.execute(query)
    student_data = cursor.fetchall()

    # 创建一个存储数据的DataFrame
    student_df = pd.DataFrame(student_data)

    # 关闭数据库连接
    cursor.close()
    connection.close()

    # 读取copy.txt文件
    with open(os.path.join(dir, 'copy.txt'), "r") as copy_file:
        lines = copy_file.readlines()

        # 遍历每一行数据
        for row in lines:
            # 遍历每一行数据
            for index, row2 in student_df.iterrows():
                # 获取学生ID和class_id2
                student_id = row2['ID']
                class_id1 = row2['class_id1']
                class_id2 = row2['class_id2']

                # 如果学生ID在当前行中，则抄袭人数加1,
                if str(student_id) in row:
                    # 读取对应的文件
                    # print(class_id2)
                    file_name = os.path.join(dir, f"{class_id2}.xlsx")
                    file_name2 = os.path.join(dir, f"class.xlsx")
                    # 如果文件存在，将file_name中class_id1所在行的抄袭人数加1，并写回原文件
                    if os.path.exists(file_name):
                        df = pd.read_excel(file_name)
                        df.loc[df['class_id1'] == class_id1, '抄袭人数'] += 1
                        # 将file_name中class_id1所在行temp列置为1
                        df.loc[df['class_id1'] == class_id1, 'temp'] = 1
                        df.to_excel(file_name, index=False)
                    if os.path.exists(file_name2):
                        df2 = pd.read_excel(file_name2)
                        df2.loc[df2['class_id2'] == class_id2, '抄袭人数'] += 1
                        df2.loc[df2['class_id2'] == class_id2, 'temp'] = 1
                        df2.to_excel(file_name2, index=False)

            # 读取分别处理1.xlsx和3.xlsx文件
            for file_name in f_list:
                # 读取文件
                with pd.ExcelFile(os.path.join(dir, file_name)) as excel:
                    # 在文件中添加两列，名称分别为抄袭人数和版本数
                    df = pd.read_excel(excel)
                    # 如果temp为1，则版本数加1
                    df.loc[df['temp'] == 1, '版本数'] += 1
                    # 将temp列置为0
                    df['temp'] = 0
                    with pd.ExcelWriter(os.path.join(dir, file_name), engine='openpyxl', mode='a',
                                        if_sheet_exists='replace') as writer:
                        df.to_excel(writer, index=False)
    # 最后处理一遍1.xlsx和3.xlsx文件
    for file_name in f_list:
        if file_name == 'class.xlsx':
            with pd.ExcelFile(os.path.join(dir, file_name)) as excel:
                # 未抄袭的人单独算一版本,将班级人数-抄袭人数，得到未抄袭人数，加到版本数中
                df = pd.read_excel(excel)
                df['版本数'] += df['班级人数'] - df['抄袭人数']
                # 删除temp列
                df = df.drop(columns=['temp'])
                with pd.ExcelWriter(os.path.join(dir, file_name), engine='openpyxl', mode='a',
                                    if_sheet_exists='replace') as writer:
                    df.to_excel(writer, index=False)
            continue
        # 读取文件
        with pd.ExcelFile(os.path.join(dir, file_name)) as excel:
            # 未抄袭的人单独算一版本,将班级人数-抄袭人数，得到未抄袭人数，加到版本数中
            df = pd.read_excel(excel)
            df['版本数'] += df['class_id1出现次数'] - df['抄袭人数']
            # 删除temp列
            df = df.drop(columns=['temp'])
            with pd.ExcelWriter(os.path.join(dir, file_name), engine='openpyxl', mode='a',
                                if_sheet_exists='replace') as writer:
                df.to_excel(writer, index=False)
    print("数据统计完成！")


# 数据存入数据库
def data_save(dir, hid):
    # 分别处理1.xlsx和3.xlsx文件
    # 连接数据库
    connection = pymysql.connect(
        host='localhost',
        user='root',
        password='A310a310',
        database=database_name,
        cursorclass=pymysql.cursors.DictCursor  # 使用字典游标以便获取列名
    )

    # 创建一个游标
    cursor = connection.cursor()

    for file_name in ['1.xlsx', '3.xlsx', 'class.xlsx']:
        # 读取文件
        with pd.ExcelFile(os.path.join(dir, file_name)) as excel:
            # 将数据存入copyversion表中,重复则替换，不重复则插入
            df = pd.read_excel(excel)
            # 文件与数据库对应关系，class_id1对应class_id1，class_id2对应class_id2，抄袭人数对应csum，版本数对应vsum,hid对应hid
            for index, row in df.iterrows():
                # 如果不存在class_id1列，则将class_id1设为‘统计大班’
                if 'class_id1' not in df.columns:
                    class_id1 = '统计大班'
                else:
                    class_id1 = row['class_id1']
                class_id2 = row['class_id2']
                csum = row['抄袭人数']
                vsum = row['版本数']
                # print(hid)
                # 检测是否存在，存在则替换，不存在则插入，不计算id
                query = "INSERT INTO copyversion(class_id1,class_id2,csum,vsum,hid) VALUES(%s,%s,%s,%s,%s) ON DUPLICATE KEY UPDATE class_id1=VALUES(class_id1),class_id2=VALUES(class_id2),csum=VALUES(csum),vsum=VALUES(vsum),hid=VALUES(hid)"
                cursor.execute(query, (class_id1, class_id2, csum, vsum, hid))
                connection.commit()

# counter('homework_uploads/1001-1')
# 调用函数并指定目录和hid参数
# data_process('homework_uploads/1001-1', '1001')
